-- psql projet_4ir
-- psql -f functionsEtudiant.sql projet_4ir
-- \q pour quitter

DROP FUNCTION IF EXISTS getListeMatieresEtudiant( VARCHAR(20) );
CREATE OR REPLACE FUNCTION getListeMatieresEtudiant( param_ine VARCHAR(20) ) RETURNS TABLE(id_matiere INT, matiere VARCHAR(50)) AS $$
BEGIN
	RETURN QUERY SELECT Q.id_cours, Mat.nom matiere FROM (SELECT unnest(E.setCourses) id_cours FROM Etudiant E WHERE E.ine = param_ine) AS Q, Matiere Mat WHERE Q.id_cours = Mat.id_matiere;	
END;
$$ LANGUAGE plpgsql;


DROP FUNCTION IF EXISTS getInformationsEtudiant( VARCHAR(50) );
CREATE OR REPLACE FUNCTION getInformationsEtudiant( mail VARCHAR(50) ) RETURNS TABLE(email VARCHAR(50), affectation Specialite, identifiant VARCHAR(20), nom VARCHAR(30), prenom VARCHAR(30)) AS $$
BEGIN
	RETURN QUERY SELECT E.email, E.affectation, E.ine, E.nom, E.prenom FROM Etudiant E WHERE E.email = mail;	
END;
$$ LANGUAGE plpgsql;

DROP FUNCTION IF EXISTS getAbsences( VARCHAR(20) );
CREATE OR REPLACE FUNCTION getAbsences(param_ine VARCHAR(20)) RETURNS TABLE(id_seance INT, matiere VARCHAR(50), id_matiere INT, isA Type_Seance, nom_enseignant VARCHAR(30), prenom_enseignant VARCHAR(30), heure TIMESTAMP, salle VARCHAR(30), justification VARCHAR(300), credibilite INT) AS $$
BEGIN
	RETURN QUERY SELECT A.id_seance, Mat.nom matiere, A.matiere id_matiere, A.isA, E.nom nom_enseignant, E.prenom prenom_enseignant, date_trunc('minute', S.heure) heure, Sa.nom salle, A.justification, A.credibilite FROM Absence A, Seance S, Enseignant E, Matiere Mat, Salle Sa WHERE A.ine = '12AR78BDC961' AND S.id_seance = A.id_seance AND A.isA = S.isA AND A.matiere = S.matiere AND S.enseignant = E.immatriculation AND A.matiere = Mat.id_matiere AND Sa.id_salle = S.salle ORDER BY S.heure DESC;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM getListeMatieresEtudiant('12AR7877C961');

SELECT * FROM getAbsences('12AR7877C961');

-- selon la matiere
SELECT A.id_seance, A.isA, A.nom_enseignant, A.prenom_enseignant, A.heure, A.salle, A.justification, A.credibilite FROM getAbsences('12AR7877C961') A WHERE A.id_matiere = 45;

--selon si c'est un cours
SELECT A.id_seance, A.nom_enseignant, A.prenom_enseignant, A.matiere, A.id_matiere, A.heure, A.salle, A.justification, A.credibilite FROM getAbsences('12AR7877C961') A WHERE A.isA = 'COURS';

--selon si c'est un TD
SELECT A.id_seance, A.nom_enseignant, A.prenom_enseignant, A.matiere, A.id_matiere, A.heure, A.salle, A.justification, A.credibilite FROM getAbsences('12AR7877C961') A WHERE A.isA = 'TDS';

--selon si c'est un TP
SELECT A.id_seance, A.nom_enseignant, A.prenom_enseignant, A.matiere, A.id_matiere, A.heure, A.salle, A.justification, A.credibilite FROM getAbsences('12AR7877C961') A WHERE A.isA = 'TPS';

--toutes les absences pour lesquelles il n'a pas donne de justifications
SELECT A.id_seance, A.nom_enseignant, A.prenom_enseignant, A.matiere, A.id_matiere, A.isA, A.heure, A.salle FROM getAbsences('12AR7877C961') A WHERE A.credibilite = 0;

SELECT * FROM getInformationsEtudiant('p.jeret@gmail.com');